﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Text;
using bzxxxt.model;
using bzxxxt.dal;
using bzxxxt.admin.service;
namespace bzxxxt.bll
{
    public class InitBLL : BaseBLL
    {
        private string[] GraveTypes = { "", "S", "D", "M" };
        private string[] GraveChars = { "", "A", "B", "C" };    
        private string oldConnStr = ConfigurationManager.ConnectionStrings["olddbconn"].ConnectionString;
        private string newConnStr = ConfigurationManager.ConnectionStrings["newconn"].ConnectionString;
        admin.entity.UserEntity user = admin.service.UserService.GetUser(HttpContext.Current);   //获取cookie里的用户对象
        private StreamWriter sw;
        private string path = "";
        private Random rdm = new Random();
        public InitBLL()
        {
            //path = HttpContext.Current.Server.MapPath("/log.txt");
            //sw = new StreamWriter(path);
            //sw.Close();
        }

        public void testdata1()
        {
            Random rdm = new Random();

            List<tbParadise> tpList = new List<tbParadise>();

            //地区
            var taList = DB.tbArea.Where(m => m.pcode != null).ToList();
            var zhengList = DB.tbArea.Where(m => m.pcode == null).ToList();
            //骨灰堂
            for (int i = 0; i < 10; i++)
            {
                string scode = zhengList[i % zhengList.Count].scode + "1";                                
                tbParadise p = new tbParadise
                {
                    code = scode,
                    name = zhengList[i%zhengList.Count].name + "骨灰堂",
                    fullName = zhengList[i % zhengList.Count].name + "骨灰堂",
                    type = "骨灰堂",
                    pic = "/admin/images/paradise.jpg",
                    crdate = DateTime.Now,
                    tbArea = zhengList[i % zhengList.Count],
                };
                DB.tbParadise.Add(p);
                tpList.Add(p);
            }

            //联系人
            for (int i = 0; i < 100; i++)
            {
                tbRelative r = new tbRelative
                {
                    cardtype = "身份证",
                    cardno = string.Format("{0:000000000000000000}", i + 1),
                    name = string.Format("联系人{0:000}", i + 1),
                    gender = i % 2 == 0 ? "男" : "女",
                    tel = string.Format("139{0:00000000}", i + 1),
                    tbArea = taList[rdm.Next(taList.Count)],
                    postcode = string.Format("{0:000000}", i % 10 + 1),
                    addtime = DateTime.Now
                };
                DB.tbRelative.Add(r);
            }

            //死者
            for (int i = 0; i < 100; i++)
            {
                tbDead r = new tbDead
                {
                    cardtype = "身份证",
                    cardno = string.Format("1{0:00000000000000000}", i + 1),
                    name = string.Format("死者{0:000}", i + 1),
                    gender = i % 2 == 1 ? "男" : "女",
                    tbArea = taList[rdm.Next(taList.Count)],
                    birthday = DateTime.Now.Date.AddYears(-rdm.Next(100)),
                    deathtime = DateTime.Now.AddYears(-rdm.Next(3)).AddMonths(-rdm.Next(12)),
                    photo = "/admin/images/dead.jpg",
                    addtime = DateTime.Now
                };
                DB.tbDead.Add(r);
            }

            DB.SaveChanges();

        }

        public void testdata2() {

            var tpList = DB.tbParadise.ToList();
            //墓穴
            for (int i = 0; i < 10; i++)
            {

                for (int sec = 1; sec <= 2; sec++)
                {
                    for (int line = 1; line <= 4; line++)
                    {
                        for (int row = 1; row <= 4; row++)
                        {
                            for (int col = 1; col <= 5; col++)
                            {
                                string name = string.Format("{1:0}栋{2:0}楼{3:0}区{4:00}排{5:00}层{6:000}号",
                                    i + 1,
                               1,
                               1,
                               sec,
                               line,
                               row,
                               col);
                                string loc = string.Format("{0}{1:0}{2:0}{3:0}{4:00}{5:00}{6:000}",
                                         tpList[i].code,
                               1,
                               1,
                               sec,
                               line,
                               row,
                               col);
                                tbGrave g = new tbGrave()
                                {
                                    paradiseId=tpList[i].id,
                                    hourseNum = 1 + "",
                                    floorNum = 1 + "",
                                    sectionNum = sec + "",
                                    lineNum = string.Format("{0:00}", line),
                                    layerNum = string.Format("{0:00}", row),
                                    columnNum = string.Format("{0:000}", col),
                                    name = name,
                                    qty = 0,
                                    location = loc,
                                    graveType = col % 3 + 1
                                };
                                DB.tbGrave.Add(g);
                            }
                        }
                    }
                }
            }

            DB.SaveChanges();
        }

        public void testdata3() {
            Random rdm = new Random();

            List<int> rList = new List<int>();
            var rs = DB.tbRelative.ToList();
            rs.ForEach(m => rList.Add(m.id));
            
            var graves = DB.tbGrave;
            var gList = graves.Select(m => m.id).ToList();
            //模拟出售

            List<int> salesGidList = new List<int>();
            
            rList.ForEach(m =>
            {
                int i = rdm.Next(gList.Count());
                int id = gList[i];
                var g = graves.FirstOrDefault(gg => gg.id == id);
                while (g.relativeId != null)
                {
                    i = rdm.Next(gList.Count());
                    id = gList[i];
                    g = graves.FirstOrDefault(gg => gg.id == id);
                }
                salesGidList.Add(g.id);
                g.relativeId = m;                
                g.saleTime = DateTime.Now;
                DB.SaveChanges();
            });

            //模拟存灰
            var ds = DB.tbDead.ToList();

            for (int i = 0; i < ds.Count; i++)
            {
                int id = ds[i].id;
                var d = DB.tbDead.FirstOrDefault(m => m.id == id);
                int x = salesGidList[i];
                d.graveid = x;                
                var g = DB.tbGrave.FirstOrDefault(m => m.id == x);
                g.qty++;
                d.relativeid = g.relativeId;               
                d.starttime = d.deathtime.Value.AddDays(rdm.Next(10));
                d.code = g.location + GraveTypes[g.graveType % 4] + GraveChars[g.qty % 4];     
                DB.SaveChanges();
            }

            
        }

        

        public void InitAll()
        {
            InitParadiseUser();
            InitDepartment();
            //InitRelative();
            InitGrave2();
        }

        private void Log(string msg)
        {
            //sw = new StreamWriter(new FileStream(path, FileMode.Append));
            //sw.WriteLine(string.Format("{0:yyyyMMdd HH:mm:ss}: {1}", DateTime.Now, msg));
            //sw.Close();
        }
        //从vipclass表中获取数据，初始化管理员和骨灰堂
        public void InitParadiseUser()
        {
            Log("start init ParadiseUser ...");
            HttpContext.Current.Response.Write("start init ParadiseUser ...<br/>");
            HttpContext.Current.Response.Flush();

            tbUser currentUser = DB.tbUser.FirstOrDefault(m => m.id == user.id);
            string sql = "select * from vipclass where vipclass=2 order by zheng";
            SqlDataAdapter da = new SqlDataAdapter(sql, oldConnStr);
            DataTable dt = new DataTable();
            da.Fill(dt);
            string prevArea = "";
            int cnt = 1;
            foreach (DataRow dr in dt.Rows)
            {
                string areaName = dr["zheng"].ToString().Trim();
                string name = dr["othername"].ToString().Trim();
                string fullName = dr["daiwei"].ToString().Trim();
                string userId = dr["username"].ToString().Trim();
                string pwd = dr["userpwd"].ToString().Trim();
                pwd = Md5.GetMD5String(pwd);   //加密
                if (areaName != prevArea) cnt = 1;
                prevArea = areaName;

                var dep = DB.tbArea.FirstOrDefault(m => m.name == areaName);

                tbParadise p = new tbParadise
                {
                    name = name,
                    fullName = fullName,
                    areacode = dep.code,
                    code = dep.code + cnt,
                    paradiseNum = 1,
                    pic = "/admin/images/paradise.jpg",
                    singles = 0,
                    doubles = 0,
                    multis = 0,
                    esingles = 0,
                    edoubles = 0,
                    emultis = 0,
                    graves = 0,
                    crdate = DateTime.Now
                };
                tbUser user = new tbUser
                {
                    userName = userId,
                    userPwd = pwd,
                    able = true,
                    points = 0,
                    grade = 1,
                    realName = userId,
                    addDate = DateTime.Now
                };


                DB.tbUserParadise.Add(new tbUserParadise
                {
                    tbParadise = p,
                    tbUser = user,
                    addTime = DateTime.Now
                });
                DB.tbUserParadise.Add(new tbUserParadise
                {
                    tbParadise = p,
                    tbUser = currentUser,
                    addTime = DateTime.Now
                });
                cnt++;

                HttpContext.Current.Response.Write(".");
                HttpContext.Current.Response.Flush();
            }
            DB.SaveChanges();
            Log("init ParadiseUser end");
            HttpContext.Current.Response.Write("init ParadiseUser end<br/>");
            HttpContext.Current.Response.Flush();
        }



        public void InitDepartment()
        {
            Log("start init department ...");
            HttpContext.Current.Response.Write("start init department ...<br/>");
            HttpContext.Current.Response.Flush();
            tbUser currentUser = DB.tbUser.FirstOrDefault(m => m.id == user.id);
            string sql = "select  distinct 村居委,所在镇 from lianxi where not 村居委 is null and 村居委!='' order by 所在镇";
            SqlDataAdapter da = new SqlDataAdapter(sql, oldConnStr);
            DataTable dt = new DataTable();
            da.Fill(dt);
            Log("load data end");
            HttpContext.Current.Response.Write("load data end <br/>");
            HttpContext.Current.Response.Flush();
            int cnt = 1;
            string preZheng = "";
            int parentId = 1;
            foreach (DataRow dr in dt.Rows)
            {
                string cun = dr["村居委"].ToString().Trim();
                string zheng = dr["所在镇"].ToString().Trim();
                if (string.IsNullOrEmpty(cun)) continue;
                if (zheng != preZheng)
                {
                    cnt = 1;
                    preZheng = zheng;
                }
                var dep = DB.tbDepartment.FirstOrDefault(m => m.name == zheng);
                if (dep == null)
                {
                    parentId = 1;
                }
                else
                {
                    parentId = dep.id;
                }
                tbDepartment d = new tbDepartment
                {
                    name = cun,
                    code = string.Format("{0:00}", cnt),
                    parentId = parentId,
                    sort = 10,
                    addDate = DateTime.Now
                };
                DB.tbDepartment.Add(d);
                cnt++;
                HttpContext.Current.Response.Write(".");
                HttpContext.Current.Response.Flush();
            }
            DB.SaveChanges();
            Log("init department end");
            HttpContext.Current.Response.Write("init department end <br/>");
            HttpContext.Current.Response.Flush();
        }

        public void InitRelative()
        {
            Log("start ...");
            tbUser currentUser = DB.tbUser.FirstOrDefault(m => m.id == user.id);
            string sql = "select distinct 0 as id, rtrim(身份证号) as cardno,rtrim(联系姓名) as name,rtrim(性别) as gender, rtrim(联系电话) as tel,rtrim(家庭住址) as addr,rtrim(单位) as company ,rtrim(职务) as title, null as areaId, 村居委,所在镇 from lianxi where not (村居委 is null or 村居委='') and 位图 like '%x2.gif%' order by 村居委";
            //string sql = "select distinct relativeId, 身份证号,联系姓名,性别, 联系电话,家庭住址,单位,职务, null as areaId,村居委,所在镇 from lianxi where not (村居委 is null or 村居委='') and 位图 like '%x2.gif%' order by 村居委";
            SqlDataAdapter da = new SqlDataAdapter(sql, oldConnStr);
            SqlCommandBuilder sc = new SqlCommandBuilder(da);
            SqlConnection conn = new SqlConnection(newConnStr);
            conn.Open();
            SqlTransaction trans = conn.BeginTransaction();
            string destSql = "select id,cardno,name,gender,tel,addr,company,title,areaId from tbRelative";
            SqlDataAdapter destDa = new SqlDataAdapter(destSql, conn);
            SqlCommandBuilder cb = new SqlCommandBuilder(destDa);
            destDa.SelectCommand.Transaction = trans;
            destDa.InsertCommand = cb.GetInsertCommand();
            destDa.InsertCommand.Transaction = trans;
            destDa.UpdateCommand = cb.GetUpdateCommand();
            destDa.UpdateCommand.Transaction = trans;
            destDa.DeleteCommand = cb.GetDeleteCommand();
            destDa.DeleteCommand.Transaction = trans;
            DataTable destT = new DataTable();
            destDa.Fill(destT);

            DataTable dt = new DataTable();
            da.Fill(dt);
            Log("query end ...");
            int i = 1;
            int areaId = 0;
            string preCunName = "";
            StringBuilder sb = new StringBuilder();
            HttpContext.Current.Response.Write("start ...<br/>");
            HttpContext.Current.Response.Flush();
            foreach (DataRow dr in dt.Rows)
            {
                string cunName = dr["村居委"].ToString().Trim();
                if (string.IsNullOrEmpty(cunName)) continue;
                if (cunName != preCunName)
                {
                    var dep = DB.tbDepartment.FirstOrDefault(m => m.name == cunName);
                    areaId = dep.id;
                    preCunName = cunName;
                }
                dr["areaId"] = areaId;
                string username = dr["name"].ToString().Trim();
                if (string.IsNullOrEmpty(username))
                {
                    username = "无名";
                }
                DataRow newDr = destT.NewRow();
                for (int k = 0; k < destT.Columns.Count; k++)
                {
                    newDr[k] = dr[k];
                }
                destT.Rows.Add(newDr);


                sb.Append(" " + i++);
                if (i % 1000 == 0)
                {
                    Log(i + "&nbsp;");
                    sb.Append("<br/>");
                    HttpContext.Current.Response.Write(sb.ToString());
                    HttpContext.Current.Response.Flush();
                    sb.Clear();
                }
            }
            Log(i + " 处理完成，开始保存...");
            HttpContext.Current.Response.Write(sb.ToString());
            HttpContext.Current.Response.Write(i + " 处理完成，开始保存...<br/>");
            HttpContext.Current.Response.Flush();

            try
            {
                destDa.Update(destT);
                trans.Commit();


            }
            catch (Exception ex)
            {
                Log("保存出错，事务回滚! " + ex.Message);
                trans.Rollback();
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }

            Log("保存完成!");
            HttpContext.Current.Response.Write("保存完成!<br/>");
            HttpContext.Current.Response.Flush();
        }


        private string translate(string section, out string outsec)
        {
            string[] numsA = { "一", "二", "三", "四", "五", "六", "七", "八", "九" };
            string[] numsB = { "1", "2", "3", "4", "5", "6", "7", "8", "9" };
            string[] numsC = { "东", "南", "西", "北" };
            string[] numsD = { "E", "S", "W", "N" };
            for (int i = 0; i < numsA.Length; i++)
            {
                section = section.Replace(numsA[i], numsB[i]);
            }
            for (int i = 0; i < numsC.Length; i++)
            {
                section = section.Replace(numsC[i], numsD[i]);
            }
            section = section.Replace("大", "L");
            section = section.Replace("小", "S");
            section = section.Replace("区", "");
            string floor = section.Replace("豪华", "HF");
            int x = section.IndexOf('层');
            if (x != -1)
            {
                floor = section.Substring(0, x);
                outsec = section.Substring(x + 1);
            }
            else
            {
                floor = "1";
                outsec = section;
            }
            if (outsec.Length < 2) outsec = "X" + outsec;
            if (outsec.Length > 2)
            {
                outsec = outsec.Substring(outsec.Length - 2);
                outsec = outsec[0] + "" + (outsec[1] - 'A' + 1);
                outsec = outsec.Substring(outsec.Length - 2, 2);
            }
            return floor;
        }

        public void InitGrave()
        {
            Log("start init grave ...");
            tbUser currentUser = DB.tbUser.FirstOrDefault(m => m.id == user.id);
            string sql = "select 位置 as location,骨灰堂名 as paradiseName,区 as sectionNum,排 as lineNum,号 as colNum,穴数 as gravetype " +
                         "from lianxi where rtrim(骨灰堂名)+rtrim(区)+rtrim(排)+rtrim(号) in (" +
                                "select A from( select rtrim(骨灰堂名)+rtrim(区)+rtrim(排)+rtrim(号) as A " +
                                    "from  lianxi) T group by A having count(A)=1) " +
                            "order by 骨灰堂名";
            SqlDataAdapter da = new SqlDataAdapter(sql, oldConnStr);
            DataTable dt = new DataTable();
            da.Fill(dt);
            string destSql = "select id,name,location,hourseNum,floorNum,sectionNum,lineNum,layerNum,columnNum,gravetype,qty,price,paradiseId,description from tbGrave ";
            SqlConnection conn = new SqlConnection(newConnStr);
            conn.Open();
            SqlTransaction trans = conn.BeginTransaction();
            SqlDataAdapter destDa = new SqlDataAdapter(destSql, conn);
            SqlCommandBuilder cb = new SqlCommandBuilder(destDa);
            destDa.SelectCommand.Transaction = trans;
            destDa.InsertCommand = cb.GetInsertCommand();
            destDa.InsertCommand.Transaction = trans;
            destDa.UpdateCommand = cb.GetUpdateCommand();
            destDa.UpdateCommand.Transaction = trans;
            destDa.DeleteCommand = cb.GetDeleteCommand();
            destDa.DeleteCommand.Transaction = trans;

            DataTable destT = new DataTable();
            destDa.Fill(destT);

            int i = 1;
            StringBuilder sb = new StringBuilder();
            HttpContext.Current.Response.Write("start ...<br/>");
            HttpContext.Current.Response.Flush();
            string preParadiseName = "";
            int paradiseId = 0;
            string paradiseCode = "";
            foreach (DataRow dr in dt.Rows)
            {
                string loc = dr["location"].ToString().Trim();
                string paradiseName = dr["paradiseName"].ToString().Trim();

                try
                {
                    string section = dr["sectionNum"].ToString().Trim();
                    string line = dr["lineNum"].ToString().Trim();
                    string col = dr["colNum"].ToString().Trim();
                    string type = dr["gravetype"].ToString().Trim();
                    if (paradiseName != preParadiseName)
                    {
                        var p = DB.tbParadise.FirstOrDefault(m => m.name == paradiseName || m.otherName.Contains(paradiseName));
                        paradiseId = p.id;
                        paradiseCode = p.code;
                        preParadiseName = paradiseName;
                    }

                    string sectioNum;
                    //区号进行转换，得到楼层号和区号
                    string floor = translate(section, out sectioNum);

                    int floorNum = int.Parse(floor);
                    floorNum = floorNum % 10;
                    ////将首字母转换成数字
                    //char c = 'A';
                    //if (char.IsLetter(line[0]))
                    //{
                    //    c = line[0];
                    //    line = line.Substring(1);
                    //    line = (c - 'A' + 1) + "" + line;
                    //}
                    //if (line.Length < 2) line = "0" + line;
                    //if (char.IsLetter(col[0]))
                    //{
                    //    c = col[0];
                    //    col = col.Substring(1);
                    //    col = (c - 'A' + 1) + "" + col;
                    //}
                    //将排与列拼接成 排排层层列列列  的方式
                    string str = line + col;
                    if (col.Length > 6 && char.IsLetter(col[0]))
                    {
                        col = col.Substring(1);
                        str = col;
                    }

                    string x = "X";
                    while (str.Length < 7)
                    {
                        str = line + x + col;
                        x = "X" + x;
                    }
                    //str = str.Substring(str.Length-7, 7);
                    string colNum = str.Substring(str.Length - 3, 3);
                    string layerNum = str.Substring(str.Length - 5, 2);
                    string lineNum = str.Substring(0, 2);
                    int typeNum = int.Parse(type);
                    int hourseNum = 1;

                    string location = string.Format("{0}{1}{2}{3}{4}{5}{6}", paradiseCode, hourseNum, floorNum, sectioNum, lineNum, layerNum, colNum);
                    string name = string.Format("{0}橦{1}楼{2}区{3}排{4}层{5}", hourseNum, floor, sectioNum, lineNum, layerNum, colNum);

                    DataRow destR = destT.NewRow();
                    destR["name"] = name;
                    destR["location"] = location;
                    destR["hourseNum"] = hourseNum;
                    destR["floorNum"] = floorNum;
                    destR["sectionNum"] = sectioNum;
                    destR["lineNum"] = lineNum;
                    destR["layerNum"] = layerNum;
                    destR["columnNum"] = colNum;
                    destR["gravetype"] = type;
                    destR["qty"] = 0;
                    destR["price"] = 0;
                    destR["paradiseId"] = paradiseId;
                    destR["description"] = loc; //保持原来的位置，以便查询
                    destT.Rows.Add(destR);
                    sb.AppendFormat(" {2}:{0}=={1} ", loc, location, i++);
                    if (i % 1000 == 0)
                    {
                        sb.Append("<br/>");
                        HttpContext.Current.Response.Write(sb.ToString());
                        HttpContext.Current.Response.Flush();
                        sb.Clear();
                    }
                }
                catch (Exception ex)
                {
                    Log(loc + " : " + ex.Message);
                    HttpContext.Current.Response.Write(loc + " : " + ex.Message);
                    HttpContext.Current.Response.Flush();
                }
            }
            Log(i + " 处理完成，开始保存...");
            HttpContext.Current.Response.Write(sb.ToString());
            HttpContext.Current.Response.Write(i + " 处理完成，开始保存...<br/>");
            try
            {
                destDa.ContinueUpdateOnError = false;
                destDa.Update(destT);
                trans.Commit();
            }
            catch (Exception ex)
            {
                Log("保存出错，事务回滚! " + ex.Message);
                HttpContext.Current.Response.Write("保存出错，事务回滚! " + ex.Message);
                HttpContext.Current.Response.Flush();
                trans.Rollback();
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            Log("保存完成!");
            HttpContext.Current.Response.Write("保存完成!<br/>");
            HttpContext.Current.Response.Flush();
        }

        //84409391
        public void InitGrave2()
        {
            Log("start init grave ...");
            HttpContext.Current.Response.Write("start init grave ...<br/>");
            HttpContext.Current.Response.Flush();
            tbUser currentUser = DB.tbUser.FirstOrDefault(m => m.id == user.id);
            string sql = "select top 1000 '' as graveName, rtrim(zang.位置) as location, '1' as hourseNum, '1' as floorNum," +
                            "rtrim(区) as sectionNum,rtrim(排) as lineNum,'XX' as layerNum,rtrim(号) as columnNum," +
                            "穴数 as gravetype , 骨灰盒数 as boneqty, null as paradiseId,rtrim(zang.位置) as description," +
                            "rtrim(亡故者姓名) as deadName,'未知' as deadGender, rtrim(与联系者关系) as relation, rtrim(骨灰类型) as bonetype, 存放日期 as addTime," +
                            "rtrim(联系姓名) as relativeName,rtrim(身份证号) as relativeCardno,rtrim(性别) as relativeGender," +
                            "rtrim(联系电话) as relativeTel,rtrim(家庭住址) as relativeAddr,rtrim(单位) as relativeCompany ,rtrim(职务) as relativeTitle, " +
                            "null as relativeAreaId,rtrim(村居委) as cunname,rtrim(所在镇) as zhengname, rtrim(zang.骨灰堂名) as paradiseName " +
                            "from zang,lianxi where zang.位置=lianxi.位置 " +
                                "and rtrim(zang.骨灰堂名)+rtrim(区)+rtrim(排)+rtrim(号) in (" +
                                    "select A from( select rtrim(骨灰堂名)+rtrim(区)+rtrim(排)+rtrim(号) as A " +
                                    "from  lianxi) T group by A having count(A)=1) ";
            SqlDataAdapter da = new SqlDataAdapter(sql, oldConnStr);
            DataTable dt = new DataTable();
            da.Fill(dt);

            SqlDataAdapter destDa = new SqlDataAdapter();
            SqlConnection conn = new SqlConnection(newConnStr);
            conn.Open();
            SqlTransaction trans = conn.BeginTransaction();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.Transaction = trans;
            destDa.InsertCommand = cmd;
            //cmd.CommandText = "declare @gid int; select @gid=id from tbGrave where description=@description; if @gid is null begin insert tbGrave(name,location,hourseNum,floorNum,sectionNum,lineNum,layerNum,columnNum,gravetype,qty,price,paradiseId,description) "
            //            + "values(@name,@location,@hourseNum,@floorNum,@sectionNum,@lineNum,@layerNum,@columnNum,@gravetype,@qty,@price,@paradiseId,@description);select @@identity;end else select @gid;";
            cmd.CommandText = "sp_import_grave_dead_relative";
            cmd.CommandType = CommandType.StoredProcedure;
            //alter proc sp_import_grave_dead_relative
            //    @graveName varchar(50),	@location varchar(50),@hourseNum varchar(4), @floorNum varchar(4),
            //    @sectionNum varchar(4), @lineNum varchar(4), @layerNum varchar(4), @columnNum varchar(6),
            //    @gravetype int,@boneqty int,  @paradiseId int, @description varchar(50),

            //    @deadName varchar(50),@deadGender varchar(2), @relation varchar(50), @bonetype varchar(50), @addTime datetime,

            //    @relativeName varchar(50), @relativeCardno varchar(50), @relativeGender varchar(4),
            //    @relativeTel varchar(50), @relativeAddr varchar(50), @relativeCompany varchar(50), @relativeTitle varchar(50),
            //    @relativeAreaId int
            // as
            SqlParameter pgraveName = new SqlParameter("@graveName", SqlDbType.VarChar, 50, "graveName");
            SqlParameter plocation = new SqlParameter("@location", SqlDbType.VarChar, 16, "location");
            SqlParameter phourseNum = new SqlParameter("@hourseNum", SqlDbType.VarChar, 2, "hourseNum");
            SqlParameter pfloorNum = new SqlParameter("@floorNum", SqlDbType.VarChar, 2, "floorNum");

            SqlParameter psectionNum = new SqlParameter("@sectionNum", SqlDbType.VarChar, 4, "sectionNum");
            SqlParameter plineNum = new SqlParameter("@lineNum", SqlDbType.VarChar, 4, "lineNum");
            SqlParameter playerNum = new SqlParameter("@layerNum", SqlDbType.VarChar, 4, "layerNum");
            SqlParameter pcolumnNum = new SqlParameter("@columnNum", SqlDbType.VarChar, 6, "columnNum");

            SqlParameter pgravetype = new SqlParameter("@gravetype", SqlDbType.Int, 4, "gravetype");
            SqlParameter pboneqty = new SqlParameter("@boneqty", SqlDbType.Int, 4, "boneqty");
            SqlParameter pparadiseId = new SqlParameter("@paradiseId", SqlDbType.Int, 4, "paradiseId");
            SqlParameter pdescription = new SqlParameter("@description", SqlDbType.VarChar, 255, "description");

            SqlParameter pdeadName = new SqlParameter("@deadName", SqlDbType.VarChar, 50, "deadName");
            SqlParameter pdeadGender = new SqlParameter("@deadGender", SqlDbType.VarChar, 50, "deadGender");
            SqlParameter prelation = new SqlParameter("@relation", SqlDbType.VarChar, 50, "relation");
            SqlParameter pbonetype = new SqlParameter("@bonetype", SqlDbType.VarChar, 50, "bonetype");
            SqlParameter paddTime = new SqlParameter("@addTime", SqlDbType.DateTime, 8, "addTime");

            SqlParameter prelativeName = new SqlParameter("@relativeName", SqlDbType.VarChar, 50, "relativeName");
            SqlParameter prelativeCardno = new SqlParameter("@relativeCardno", SqlDbType.VarChar, 50, "relativeCardno");
            SqlParameter prelativeGender = new SqlParameter("@relativeGender", SqlDbType.VarChar, 50, "relativeGender");
            SqlParameter prelativeTel = new SqlParameter("@relativeTel", SqlDbType.VarChar, 50, "relativeTel");
            SqlParameter prelativeAddr = new SqlParameter("@relativeAddr", SqlDbType.VarChar, 50, "relativeAddr");

            SqlParameter prelativeCompany = new SqlParameter("@relativeCompany", SqlDbType.VarChar, 50, "relativeCompany");
            SqlParameter prelativeTitle = new SqlParameter("@relativeTitle", SqlDbType.VarChar, 50, "relativeTitle");
            SqlParameter prelativeAreaId = new SqlParameter("@relativeAreaId", SqlDbType.Int, 4, "relativeAreaId");

            cmd.Parameters.AddRange(new SqlParameter[] { 
                            pgraveName,
                            plocation,
                            phourseNum,
                            pfloorNum,

                            psectionNum,
                            plineNum,
                            playerNum,
                            pcolumnNum,

                            pgravetype,
                            pboneqty,
                            pparadiseId,
                            pdescription,

                            pdeadName,
                            pdeadGender,
                            prelation,
                            pbonetype,
                            paddTime,

                            prelativeName,
                            prelativeCardno,
                            prelativeGender,
                            prelativeTel,
                            prelativeAddr,

                            prelativeCompany,
                            prelativeTitle,
                            prelativeAreaId

                    });
            DataTable destT = dt.Clone();


            StringBuilder sb = new StringBuilder();
            int i = 1;
            string preParadiseName = "";
            int paradiseId = 0;
            string paradiseCode = "";
            int areaId = 0;
            string preCunName = "";

            foreach (DataRow dr in dt.Rows)
            {
                string loc = dr["location"].ToString().Trim();
                string paradiseName = dr["paradiseName"].ToString().Trim();
                try
                {
                    string section = dr["sectionNum"].ToString().Trim();
                    string line = dr["lineNum"].ToString().Trim();
                    string col = dr["columnNum"].ToString().Trim();
                    string type = dr["gravetype"].ToString().Trim();
                    string qty = dr["boneqty"].ToString().Trim();
                    if (paradiseName != preParadiseName)
                    {
                        var p = DB.tbParadise.FirstOrDefault(m => m.name == paradiseName || m.otherName.Contains(paradiseName));
                        paradiseId = p.id;
                        paradiseCode = p.code;
                        preParadiseName = paradiseName;
                    }

                    string sectionNum;
                    //区号进行转换，得到楼层号和区号
                    string floor = translate(section, out sectionNum);

                    int floorNum = int.Parse(floor);
                    floorNum = floorNum % 10;
                    string str = line + col;
                    if (col.Length > 6 && char.IsLetter(col[0]))
                    {
                        col = col.Substring(1);
                        str = col;
                    }

                    string x = "X";
                    while (str.Length < 7)
                    {
                        str = line + x + col;
                        x = "X" + x;
                    }
                    //str = str.Substring(str.Length-7, 7);
                    string colNum = str.Substring(str.Length - 3, 3);
                    string layerNum = str.Substring(str.Length - 5, 2);
                    string lineNum = str.Substring(0, 2);
                    int gravetype = int.Parse(type);
                    int hourseNum = 1;

                    string location = string.Format("{0}{1}{2}{3}{4}{5}{6}", paradiseCode, hourseNum, floorNum, sectionNum, lineNum, layerNum, colNum);
                    string graveName = string.Format("{0}橦{1}楼{2}区{3}排{4}层{5}", hourseNum, floor, sectionNum, lineNum, layerNum, colNum);
                    //穴位信息
                    dr["graveName"] = graveName;
                    dr["location"] = location;
                    dr["hourseNum"] = hourseNum;
                    dr["floorNum"] = floorNum;
                    dr["sectionNum"] = sectionNum;
                    dr["lineNum"] = lineNum;
                    dr["layerNum"] = layerNum;
                    dr["columnNum"] = colNum;
                    dr["gravetype"] = gravetype;
                    dr["boneqty"] = int.Parse(qty);
                    dr["paradiseId"] = paradiseId;
                    //联系人信息
                    string cunName = dr["cunName"].ToString().Trim();
                    if (string.IsNullOrEmpty(cunName)) continue;
                    if (cunName != preCunName)
                    {
                        var dep = DB.tbDepartment.FirstOrDefault(m => m.name == cunName);
                        areaId = dep.id;
                        preCunName = cunName;
                    }
                    dr["relativeAreaId"] = areaId;
                    string username = dr["relativeName"].ToString().Trim();
                    if (string.IsNullOrEmpty(username))
                    {
                        dr["relativeName"] = "无名";
                    }
                    //死者信息
                    string deadName = dr["deadName"].ToString().Trim();
                    if (string.IsNullOrEmpty(deadName))
                    {
                        dr["deadName"] = "未知";
                    }
                    string deadGender = dr["deadGender"].ToString().Trim();
                    if (string.IsNullOrEmpty(deadGender))
                    {
                        dr["deadGender"] = "未知";
                    }

                    DataRow tr = destT.NewRow();
                    for (int k = 0; k < destT.Columns.Count; k++)
                    {
                        tr[k] = dr[k];
                    }
                    destT.Rows.Add(tr);

                    sb.AppendFormat(" {0}:{1}=={2} ", i++, loc, location);
                    if (i % 1000 == 0)
                    {
                        sb.Append("<br/>");
                        HttpContext.Current.Response.Write(sb.ToString());
                        HttpContext.Current.Response.Flush();
                        sb.Clear();
                    }
                }
                catch (Exception ex)
                {
                    Log(loc + " : " + ex.Message);
                    HttpContext.Current.Response.Write(loc + " : " + ex.Message);
                    HttpContext.Current.Response.Flush();
                }
            }
            HttpContext.Current.Response.Write(sb.ToString());
            HttpContext.Current.Response.Write(i + " 处理完成，开始提交...<br/>");
            try
            {
                destDa.Update(destT);
                trans.Commit();
                Log("提交成功!");
                HttpContext.Current.Response.Write("提交成功!<br/>");
                HttpContext.Current.Response.Flush();
            }
            catch (Exception ex)
            {
                Log("提交出错，事务回滚! " + ex.Message);
                HttpContext.Current.Response.Write("提交出错，事务回滚! " + ex.Message);
                HttpContext.Current.Response.Flush();
                trans.Rollback();
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }

        public List<AreaModel> getCityList(string url) {
            string baseURL = getBaseURL(url);
            string html = JsonUtil.HttpGet(url);
            HtmlAgilityPack.HtmlDocument hd = new HtmlAgilityPack.HtmlDocument();
            hd.LoadHtml(html);
            HtmlAgilityPack.HtmlNodeCollection nc = hd.DocumentNode.SelectNodes("//tr[@class='citytr']");
            List<AreaModel> list = new List<AreaModel>();
            foreach (var node in nc) {
                HtmlAgilityPack.HtmlNodeCollection n = node.SelectNodes("td/a");
                if (n != null && n.Count > 0)
                {
                    AreaModel am = new AreaModel
                    {
                        code = n[0].InnerText,
                        name = n[1].InnerText,
                        url = baseURL + n[0].Attributes["href"].Value
                    };
                    list.Add(am);
                    List<AreaModel> cList = getCountyList(am.url);
                    am.children.AddRange(cList);
                }
                else
                {
                    n = node.SelectNodes("td");
                    AreaModel am = new AreaModel
                    {
                        code = n[0].InnerText,
                        name = n[1].InnerText
                    };
                    list.Add(am);
                }
            }
            return list;
        }

        public List<AreaModel> getCountyList(string url) {
            string baseURL = getBaseURL(url);
            string html = JsonUtil.HttpGet(url);
            HtmlAgilityPack.HtmlDocument hd = new HtmlAgilityPack.HtmlDocument();
            hd.LoadHtml(html);
            HtmlAgilityPack.HtmlNodeCollection nc = hd.DocumentNode.SelectNodes("//tr[@class='countytr']");
            List<AreaModel> list = new List<AreaModel>();
            foreach (var node in nc) {
                HtmlAgilityPack.HtmlNodeCollection n = node.SelectNodes("td/a");
                if (n != null && n.Count > 0)
                {
                    AreaModel am = new AreaModel
                    {
                        code = n[0].InnerText,
                        name = n[1].InnerText,
                        url = baseURL + n[0].Attributes["href"].Value
                    };
                    list.Add(am);
                    List<AreaModel> tList = getTownList(am.url);
                    am.children.AddRange(tList);
                }
                else
                {
                    n = node.SelectNodes("td");
                    AreaModel am = new AreaModel
                    {
                        code = n[0].InnerText,
                        name = n[1].InnerText     
                    };
                    list.Add(am);
                }
            }
            return list;
        }

        public List<AreaModel> getTownList(string url)
        {
            string baseURL = getBaseURL(url);
            string html = JsonUtil.HttpGet(url);
            HtmlAgilityPack.HtmlDocument hd = new HtmlAgilityPack.HtmlDocument();
            hd.LoadHtml(html);
            HtmlAgilityPack.HtmlNodeCollection nc = hd.DocumentNode.SelectNodes("//tr[@class='towntr']");
            List<AreaModel> list = new List<AreaModel>();
            foreach (var node in nc)
            {
                HtmlAgilityPack.HtmlNodeCollection n = node.SelectNodes("td/a");
                if (n != null && n.Count > 0)
                {
                    AreaModel am = new AreaModel
                    {
                        code = n[0].InnerText,
                        name = n[1].InnerText,
                        url = baseURL + n[0].Attributes["href"].Value
                    };
                    list.Add(am);
                    List<AreaModel> vList = getVillageList(am.url);
                    am.children.AddRange(vList);
                }
                else
                {
                    n = node.SelectNodes("td");
                    AreaModel am = new AreaModel
                    {
                        code = n[0].InnerText,
                        name = n[1].InnerText
                    };
                    list.Add(am);
                }
            }
            return list;
        }

        public List<AreaModel> getVillageList(string url)
        {
            string baseURL = getBaseURL(url);
            string html = JsonUtil.HttpGet(url);
            HtmlAgilityPack.HtmlDocument hd = new HtmlAgilityPack.HtmlDocument();
            hd.LoadHtml(html);
            HtmlAgilityPack.HtmlNodeCollection nc = hd.DocumentNode.SelectNodes("//tr[@class='villagetr']");
            List<AreaModel> list = new List<AreaModel>();
            foreach (var node in nc)
            {
                HtmlAgilityPack.HtmlNodeCollection n = node.SelectNodes("td");
                AreaModel am = new AreaModel
                {
                    code = n[0].InnerText,
                    name = n[2].InnerText
                };
                list.Add(am);
            }
            return list;
        }


        public string InitTownList(List<AreaModel> list) { 
            int i = 1;
            foreach(var t in list){
                tbArea ta = new tbArea { 
                    code = t.code,
                    name = t.name,
                    
                    scode = string.Format("{0:00}",i==4?++i:i),
                    sort = i++
                };
                DB.tbArea.Add(ta);

                int k = 1;
                foreach (var v in t.children) {
                    tbArea va = new tbArea
                    {
                        code = v.code,
                        name = v.name,
                        pcode = t.code,
                        scode = ta.scode,
                        sort = k++
                    };
                    DB.tbArea.Add(va);
                }                
            }
            DB.SaveChanges();
            return "操作成功";
        }
        private string getBaseURL(string url) {
            int x = url.LastIndexOf("/");
            return url.Substring(0, x+1);
        }

        public string InitMap() {
            string sql = "select distinct rtrim(ltrim(骨灰堂名)) as pname  from lianxi";
            SqlConnection conn = new SqlConnection(newConnStr);
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            SqlDataAdapter da = new SqlDataAdapter(sql, oldConnStr);
            DataTable dt = new DataTable();
            da.Fill(dt);

            string[] exs = {"4" };
            
            foreach (DataRow dr in dt.Rows)
            {
                string pname = dr["pname"].ToString().Trim();
                //if(pname=="孝安楼") continue;
                //if (pname == "塘市骨灰堂") continue;
                sql = "select distinct rtrim(ltrim(区)) as section  from lianxi where 骨灰堂名='" + pname + "'";
                da.SelectCommand.CommandText = sql;
                DataTable dtSec = new DataTable();
                da.Fill(dtSec);
                int cnt = 1;
                string insql = "";
                foreach(DataRow dr2 in dtSec.Rows){
                    if (exs.Any(m => (cnt + "").IndexOf(m + "") >= 0)) cnt++;
                    string num = string.Format("{0:00}",cnt++);
                    string secname = dr2["section"].ToString();
                    insql += string.Format("insert tbMap(oldsection,oldline,newnum,ttype,pname) values('{0}',null,'{1}',1,'{2}')", secname, num, dr["pname"]) + ";";
                    
                    
                    //cmd.CommandText = insql;
                    //cmd.ExecuteNonQuery();      
                }
                sql = "select distinct rtrim(ltrim(排)) as line from lianxi where 骨灰堂名='" + pname + "'";
                da.SelectCommand.CommandText = sql;
                DataTable dtline = new DataTable();
                da.Fill(dtline);
                int cnt2 = 1;

                foreach (DataRow dr3 in dtline.Rows)
                {
                    string linename = dr3["line"].ToString();

                    string num2 = linename;
                    int x = 0;
                    bool b = int.TryParse(linename, out x);
                    if (!b)
                    {
                        if (exs.Any(m => (cnt2 + "").IndexOf(m + "") >= 0)) cnt2++;
                        num2 = string.Format("{0:00}", cnt2++);
                    }                    
                    insql += string.Format("insert tbMap(oldsection,oldline,newnum,ttype,pname) values(null,'{0}','{1}',2,'{2}')", linename, num2, dr["pname"]) + ";";
                }
                cmd.CommandText = insql;
                cmd.ExecuteNonQuery();      
            }
            return "ok";
        }

        public string InitMap2()
        {
            string sql = "select distinct rtrim(ltrim(骨灰堂名)) as pname  from lianxi";
            SqlConnection conn = new SqlConnection(newConnStr);
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            SqlDataAdapter da = new SqlDataAdapter(sql, oldConnStr);
            DataTable dt = new DataTable();
            da.Fill(dt);

            string[] exs = { "4" };

            foreach (DataRow dr in dt.Rows)
            {
                string pname = dr["pname"].ToString().Trim();
                //if(pname=="孝安楼") continue;
                //if (pname == "塘市骨灰堂") continue;
                sql = "select distinct rtrim(ltrim(区)) as section  from lianxi where 骨灰堂名='" + pname + "'";
                da.SelectCommand.CommandText = sql;
                DataTable dtSec = new DataTable();
                da.Fill(dtSec);
                int cnt = 1;
                string insql = "";
                foreach (DataRow dr2 in dtSec.Rows)
                {
                    if (exs.Any(m => (cnt + "").IndexOf(m + "") >= 0)) cnt++;
                    string num = string.Format("{0:00}", cnt++);
                    string secname = dr2["section"].ToString();
                    insql += string.Format("insert tbMap(oldsection,oldline,newnum,ttype,pname) values('{0}',null,'{1}',1,'{2}')", secname, num, dr["pname"]) + ";";

                    sql = "select distinct rtrim(ltrim(排)) as line from lianxi where 骨灰堂名='" + pname + "' and 区='" + secname + "'";
                    da.SelectCommand.CommandText = sql;
                    DataTable dtline = new DataTable();
                    da.Fill(dtline);
                    int cnt2 = 1;

                    foreach (DataRow dr3 in dtline.Rows)
                    {
                        string linename = dr3["line"].ToString();
                        if (exs.Any(m => (cnt2 + "").IndexOf(m + "") >= 0)) cnt2++;
                        string num2 = string.Format("{0:00}", cnt2++);
                        insql += string.Format("insert tbMap(oldsection,oldline,newnum,ttype,pname) values('{0}','{1}','{2}',2,'{3}')", secname, linename, num2, dr["pname"]) + ";";
                    }
                    //cmd.CommandText = insql;
                    //cmd.ExecuteNonQuery();      
                }
                cmd.CommandText = insql;
                cmd.ExecuteNonQuery();
            }
            return "ok";
        }
    }
}